<?php

require_once('constants.php');
require_once('appparam.php');
require_once('status.php');




/**
 * Liste des statuts
 */
define('MEMBER',		 1 << 1);
define('ADMIN',			 1 << 2);
define('MEMBER_WAITING', 1 << 3);
define('MODO',			 1 << 4);
define('MEMBER_OLD',     1 << 5);
define('MEMBER_CANCELED', 1 << 6);

/**
 * Obtenir la liste des membres
 * @param filter_flag: bitmask des statuts filtrés (ADMIN pour les admins,
 *  MEMBER | ADMIN pour les admins et les adhérents enregistrés, etc.)
 *
 * @param sorted_row: colonne de tri
 * @param is_desc: tri ascendant si false, sinon descendant

 * @param search_keyword: mot à rechercher (string)
 * ou un ensemble de mots à rechercher (string array)
 * @param search_fields: tableau des champs sur lesquel porte la recherche de $search_keyword
 * @param multi_search_logic: valable uniquement si search_keyword est un string array.
 * si OR : au moins un des termes, si AND : tous les termes
 * @return record set
 */
function member_get_list($filter_flag, $sorted_row = NULL, $is_desc = false,
	$search_keyword = NULL, $search_fields = NULL, $multi_search_logic = 'OR')
{
	// critères de filtre
	$filter = array(
		MEMBER => 'MEMBER',
		ADMIN => 'ADMIN',
		MEMBER_WAITING => 'WAITING_MEMBER',
		MODO => 'MODERATOR',
		MEMBER_OLD => 'OLD_MEMBER',
		MEMBER_CANCELED => 'CANCELED');
	$where = array();
	foreach ($filter as $status => $code) {
		if ($status & $filter_flag) {
			$where[] = 's.code = "'.$code.'"';
		}
	}

	// critères de tri
	if ($sorted_row == NULL) {
		$order_by = 'm.name, m.firstname';
	}
	else {
		/* hack :
		en BDD, la date de naissance est au format "JJ/MM/AAAA", ce qui
		empêche tout opération de tri. on transforme donc cette chaîne
		au format "AAAAMMJJ" dans la requête afin de pouvoir trier sur la chaîne
		*/
		if ($sorted_row == 'birth') {
			$order_by = 'CONCAT(SUBSTR(m.birth, 7, 4), SUBSTR(m.birth, 4, 2), SUBSTR(m.birth, 0, 2))';
		}
		else {
			$order_by = mysql_real_escape_string($sorted_row);
		}

		if ($is_desc) {
			$order_by .= ' DESC';
		}
	}

	// recherche
	$sql_search = '';
	if (is_array($search_fields)) {
		# only one keyword
		if (!is_array($search_keyword)) {
			$search_keyword = mysql_real_escape_string($search_keyword);
			$tmp_array = array();
			foreach ($search_fields as $field) {
				$tmp_array[] = sprintf('%s LIKE "%%%s%%"', $field, $search_keyword);
			}
			$sql_search = sprintf(' AND (%s)', implode(' OR ', $tmp_array));
		}
		# multi keywords
		else {

			$tmp_array = array();
			foreach ($search_fields as $field) {
				$tmp_array[] = sprintf('%s LIKE "%%#TOKEN#%%"', $field);
			}
			$pattern = '('.implode(' OR ', $tmp_array).')';

			$tmp_array = array();
			$search_keyword = array_map('mysql_real_escape_string', $search_keyword);
			foreach ($search_keyword as $keyword) {
				$tmp_array[] = str_replace('#TOKEN#', $keyword, $pattern);
			}
			$sql_search = sprintf(' AND (%s)', implode(' '.$multi_search_logic.' ', $tmp_array));
		}
	}

	$sql = 'SELECT m.id, m.password, m.date_pre_registration, m.date_registration,
	        m.birth, m.name, m.firstname, m.address, m.city, m.postal_code, m.email,
			m.homephone, m.cellphone, m.idParent, m.connection_count, m.last_connection,
			g.id AS id_gym, g.name AS gym_name,
			c.id AS id_cat, c.name AS cat_name,
			s.code AS status_code, s.label AS status_label, s.color AS status_color,
			m.gender, m.climbing_level, m.climbing_years_practice,
			m.competitions, m.has_pass92, m.need_registration_proof, m.authorize_image_use,
			m.date_registration_check, m.date_medical_certificate
	        FROM Member m
	        LEFT JOIN Gymnasium g ON m.idGymnasium = g.id
			LEFT JOIN Category c ON m.idCategory = c.id
			JOIN MemberStatus s ON m.status_id = s.id
	        WHERE ('.implode(' OR ', $where).') '.$sql_search.'
	        ORDER BY '.$order_by.';';
	$data = sql_query($sql);
	return $data;
}


function member_get_by_status($status_code)
{
	$status_code = mysql_real_escape_string($status_code);
	$sql = 'SELECT m.id, m.password, m.date_pre_registration, m.date_registration,
	        m.birth, m.name, m.firstname, m.address, m.city, m.postal_code, m.email,
			m.homephone, m.cellphone, m.idParent, m.connection_count, m.last_connection,
			m.gender, m.climbing_level, m.climbing_years_practice,
			m.competitions, m.has_pass92, m.need_registration_proof, m.authorize_image_use,
			m.date_registration_check, m.date_medical_certificate,
			g.id AS id_gym, g.name AS gym_name,
			c.id AS id_cat, c.name AS cat_name,
			s.code AS status_code, s.label AS status_label, s.color AS status_color
	        FROM Member m
	        LEFT JOIN Gymnasium g ON m.idGymnasium = g.id
			LEFT JOIN Category c ON m.idCategory = c.id
			JOIN MemberStatus s ON m.status_id = s.id
	        WHERE s.code = "'.$status_code.'";';
	$data = sql_query($sql);
	return $data;
}


/**
 * Obtenir un membre par son id
 * @param id: id du membre
 * @return array si existe, sinon NULL
 */
function member_get_by_id($id, $fetch = true)
{
	$id = intval($id);
	$sql = 'SELECT m.id, m.password, m.date_pre_registration, m.date_registration,
	        m.birth, m.name, m.firstname, m.address, m.city, m.postal_code, m.email,
			m.homephone, m.cellphone, m.idParent, m.connection_count, m.last_connection,
			g.id AS id_gym, g.name AS gym_name,
			c.id AS id_cat, c.name AS cat_name,
			s.code AS status_code, s.label AS status_label, s.color AS status_color,
			m.gender, m.climbing_level, m.climbing_years_practice,
			m.competitions, m.has_pass92, m.need_registration_proof, m.authorize_image_use,
			m.date_registration_check, m.date_medical_certificate
			FROM Member m
			LEFT JOIN Gymnasium g ON m.idGymnasium = g.id
			LEFT JOIN Category c ON m.idCategory = c.id
			JOIN MemberStatus s ON m.status_id = s.id
			WHERE m.id = '.$id.';';
	$data = sql_query($sql);
	return $fetch ? mysql_fetch_assoc($data) : $data;
}


/**
 * Connecter un membre
 * @param email: email servant de login
 * @param password: hash du mot de passe
 * @return true si connexion réussie, sinon false
 */
function member_connect($email, $password)
{
	$email = mysql_real_escape_string($email);
	// sélection du premier inscrit (id le plus petit)
	$sql = 'SELECT m.id, m.email, m.name, m.firstname,
	        s.code AS status_code, s.label AS status_label, s.color AS status_color
	        FROM Member m
			JOIN MemberStatus s ON m.status_id = s.id
	        WHERE m.email = "'.$email.'" AND m.password = "'.$password.'"
	        ORDER BY m.id;';

	$data = sql_query($sql);
	if ($row = mysql_fetch_assoc($data)) {
		$id = intval($row['id']);
		$_SESSION['is_connect'] = true;
		$_SESSION['id'] = $id;
		$_SESSION['email'] = $row['email'];
		$_SESSION['name'] = $row['name'];
		$_SESSION['firstname'] = $row['firstname'];
		$_SESSION['status_code'] = $row['status_code'];
		$_SESSION['status_label'] = '<span style="color: '.$row['status_color'].'">'.$row['status_label'].'</span>';
		member_update_connections($id);
		return true;
	}
	$_SESSION['is_connect'] = false;
	return false;
}


/**
 * Obtenir les membres par leurs noms
 * @param $name: nom à rechercher
 * @return record set
 */
function member_match_name($name)
{
	$name = mysql_real_escape_string($name);
	$sql = 'SELECT m.id, m.password, m.date_registration, m.birth,
			m.name, m.firstname, m.address, m.city, m.postal_code, m.email,
			m.homephone, m.cellphone, m.idParent,
			s.code AS status_code, s.label AS status_label, s.color AS status_color
	        FROM Member m
			JOIN MemberStatus s ON m.status_id = s.id
	        WHERE m.name LIKE "%'.$name.'%"
	        ORDER BY m.name, m.firstname;';
	$data = sql_query($sql);
	return $data;
}

/**
 * Enregistrer un membre en attente de validation
 * @return id du nouveau membre
 */
function member_register($birth, $name, $firstname, $address, $city, $postal_code,
	$email, $password, $homephone, $cellphone, $id_gym, $id_cat, $gender, $climbing_level, $climbing_years_practice,
	$competitions, $has_pass92, $need_registration_proof, $authorize_image_use)
{
	$password = crypt_password($password);
	$user_id = _member_add($password, $birth, $name, $firstname, $address, $city, $postal_code,
		$email, $homephone, $cellphone, $id_gym, $id_cat, $gender, $climbing_level, $climbing_years_practice,
	$competitions, $has_pass92, $need_registration_proof, $authorize_image_use);
	return $user_id;
}

/**
 * Enregistrer un membre en attente de validation, depuis un compte mail existant
 * @return id du nouveau membre
 */
function member_register_managed($birth, $name, $firstname, $address, $city, $postal_code,
	$email, $password, $homephone, $cellphone, $id_gym, $id_cat, $gender, $climbing_level, $climbing_years_practice,
	$competitions, $has_pass92, $need_registration_proof, $authorize_image_use)
{
	$email = mysql_real_escape_string($email);
	$sql = 'SELECT password
	        FROM Member
	        WHERE email = "'.$email.'";';
	$data = sql_query($sql);
	$row = mysql_fetch_row($data);
	$password = $row[0];

	$user_id = _member_add($password, $birth, $name, $firstname, $address, $city, $postal_code,
		$email, $homephone, $cellphone, $id_gym, $id_cat, $gender, $climbing_level, $climbing_years_practice,
	$competitions, $has_pass92, $need_registration_proof, $authorize_image_use);
	return $user_id;
}

/**
 * Ajout d'un admin
 */
function member_admin_add($name, $firstname, $email, $password, $status_code)
{
	$name = mysql_real_escape_string($name);
	$firstname = mysql_real_escape_string($firstname);
	$password = crypt_password($password);

	$status_id = status_get_from_code($status_code);

	$sql = "INSERT INTO Member (email, password, firstname, name, status_id)
			VALUES ('$email', '$password', '$firstname', '$name', $status_id);";
	sql_query($sql);

	$admin_id = mysql_insert_id();
	$sql = 'insert into MemberStatusHistory (id, user_id, creator_id, status_id, created_at)
	        values (NULL, '.$admin_id.', '.intval($_SESSION['id']).', '.$status_id.', '.time().');';
	return sql_query($sql);
}

/**
 * Modifier le statut d'un membre
 */
function member_update_status($id, $new_status_code)
{
	$id = intval($id);
	$status_id = status_get_from_code($new_status_code);

	$sql = 'insert into MemberStatusHistory (id, user_id, creator_id, status_id, created_at)
	        values (NULL, '.$id.', '.intval($_SESSION['id']).', '.$status_id.', '.time().');';
	sql_query($sql);

	$sql = "UPDATE Member
			SET status_id = $status_id
			WHERE id = $id;";
	
	if ($id == intval($_SESSION['id'])) {
		$status = status_get_by_id($status_id);
		$_SESSION['status_code'] = $status['code'];
		$_SESSION['status_label'] = '<span style="color: '.$status['color'].'">'.$status['label'].'</span>';
	}
	return sql_query($sql);
}


/**
 * Obtenir l'historique des changements de statut d'un membre
 * @return record set
 */
function member_get_history($id)
{
	$id = intval($id);
	$sql = 'select h.user_id, h.creator_id, h.created_at, h.status_id,
	        c.name as creator_name, c.firstname as creator_firstname,
	        s.label as status_label, s.color as status_color
			from MemberStatusHistory h
			join Member c on c.id = h.creator_id
			join MemberStatus s on s.id = h.status_id
			where h.user_id = '.$id.'
			order by created_at desc;';
	return sql_query($sql);
}


/**
 * Ajout d'un membre (fonction privée)
 * @return user_id
 */
function _member_add($password, $birth, $name, $firstname, $address, $city, $postal_code,
	$email, $homephone, $cellphone, $id_gym, $id_cat, $gender, $climbing_level, $climbing_years_practice,
	$competitions, $has_pass92, $need_registration_proof, $authorize_image_use)
{
	$birth = mysql_real_escape_string($birth);
	$name = mysql_real_escape_string($name);
	$firstname = mysql_real_escape_string($firstname);
	$address = mysql_real_escape_string($address);
	$city = mysql_real_escape_string($city);
	$postal_code = mysql_real_escape_string($postal_code);
	$email = mysql_real_escape_string($email);
	$homephone = mysql_real_escape_string($homephone);
	$cellphone = mysql_real_escape_string($cellphone);
	$id_gym = intval($id_gym);
	$id_cat = intval($id_cat);
	$gender = mysql_real_escape_string(strtoupper($gender));
	$climbing_level = mysql_real_escape_string($climbing_level);
	$climbing_years_practice = intval($climbing_years_practice);
	$competitions = $competitions ? 1 : 0;
	$has_pass92 = $has_pass92 ? 1 : 0;
	$need_registration_proof = $need_registration_proof ? 1 : 0;
	$authorize_image_use = $authorize_image_use ? 1 : 0;

	$status_id = status_get_from_code('WAITING_MEMBER');

	$sql = 'INSERT INTO Member (password, birth, name, firstname, address, city,
	            postal_code, email, homephone, cellphone,
	            date_pre_registration, status_id, idGymnasium, idParent, idCategory,
	            gender, climbing_level, climbing_years_practice, competitions,
	            has_pass92, need_registration_proof, authorize_image_use)
	        VALUES ("'.$password.'", "'.$birth.'", "'.$name.'", "'.$firstname.'", "'.$address.'", "'.$city.'",
	            "'.$postal_code.'", "'.$email.'", "'.$homephone.'", "'.$cellphone.'",
	            '.time().', '.$status_id.', '.$id_gym.', 0, '.$id_cat.',
	            "'.$gender.'", "'.$climbing_level.'", '.$climbing_years_practice.', '.$competitions.',
	            '.$has_pass92.', '.$need_registration_proof.', '.$authorize_image_use.');';
	sql_query($sql);

	$user_id = mysql_insert_id();
	$sql = 'insert into MemberStatusHistory (id, user_id, creator_id, status_id, created_at)
	        values (NULL, '.$user_id.', '.$user_id.', '.$status_id.', '.time().');';
	mysql_query($sql);
	return $user_id;
}

/**
 * Valider l'inscription d'un membre
 * @param id: id du membre en attente de validation
 * @return true si succès de l'email, sinon false
 */
function member_valid_registration($id)
{
	$id = intval($id);
	member_update_status($id, 'MEMBER');

	$sql = 'UPDATE Member
	        SET date_registration = '.time().'
	        WHERE id = '.$id.';';
	sql_query($sql);

	$member = member_get_by_id($id);
	return send_validation_mail($member['email'], $member['name'], $member['firstname']);
}


/**
 * Annuler la validation de l'inscription
 * @param id: id du membre à repasser en attente
 */
function member_cancel_registration($id)
{
	$id = intval($id);
	member_update_status($id, 'WAITING_MEMBER');

	$sql = 'UPDATE Member
	        SET date_registration = 0
	        WHERE id = '.$id.';';
	sql_query($sql);
}

/**
 * Réinitialise un membre en demande d'inscription
 */
function member_reset_registration($id)
{
	$id = intval($id);
	member_update_status($id, 'WAITING_MEMBER');

	$sql = 'UPDATE Member
	        SET date_registration = 0,
	        date_pre_registration = '.time().',
	        date_registration_check = NULL,
	        date_medical_certificate = NULL
	        WHERE id = '.$id.';';
	sql_query($sql);
}



/**
 * Mise à jour des infos d'un membre
 */
function member_update($id, $birth, $name, $firstname, $address, $city,
	$postal_code, $email, $homephone, $cellphone, $id_gym, $id_cat,
	$gender, $climbing_level, $climbing_years_practice,
	$competitions, $has_pass92, $need_registration_proof, $authorize_image_use)
{
	if ($_SESSION['id'] == $id) {
		$_SESSION['email'] = $email;
		$_SESSION['name'] = $name;
		$_SESSION['firstname'] = $firstname;
	}

	$id = intval($id);
	$birth = mysql_real_escape_string($birth);
	$name = mysql_real_escape_string($name);
	$firstname = mysql_real_escape_string($firstname);
	$address = mysql_real_escape_string($address);
	$city = mysql_real_escape_string($city);
	$postal_code = mysql_real_escape_string($postal_code);
	$email = mysql_real_escape_string($email);
	$homephone = mysql_real_escape_string($homephone);
	$cellphone = mysql_real_escape_string($cellphone);
	$id_gym = intval($id_gym);
	$id_cat = intval($id_cat);
	$gender = mysql_real_escape_string(strtoupper($gender));
	$climbing_level = mysql_real_escape_string($climbing_level);
	$climbing_years_practice = intval($climbing_years_practice);
	$competitions = $competitions ? 1 : 0;
	$has_pass92 = $has_pass92 ? 1 : 0;
	$need_registration_proof = $need_registration_proof ? 1 : 0;
	$authorize_image_use = $authorize_image_use ? 1 : 0;

	// si un membre mineur devient majeur, on supprime son parent
	$remove_parent = get_age($birth) >= 18;
	if ($remove_parent) {
		$data = member_get_by_id($id);
		$id_parent = $data['idParent'];
		// s'il était mineur
		if ($id_parent != 0) {
			member_delete_parent($id_parent);
		}
	}
	$sql = 'UPDATE Member
			SET birth = "'.$birth.'",
			    name = "'.$name.'",
			    firstname = "'.$firstname.'",
			    address = "'.$address.'",
			    city = "'.$city.'",
			    postal_code = "'.$postal_code.'",
			    email = "'.$email.'",
			    homephone = "'.$homephone.'",
			    cellphone = "'.$cellphone.'",
			    idGymnasium = '.$id_gym.',
			    idCategory = '.$id_cat.',
			    gender = "'.$gender.'",
				climbing_level = "'.$climbing_level.'",
				climbing_years_practice = '.$climbing_years_practice.',
				competitions = '.$competitions.',
				has_pass92 = '.$has_pass92.',
				need_registration_proof = '.$need_registration_proof.',
				authorize_image_use = '.$authorize_image_use;
	if ($remove_parent) {
		$sql .= ', idParent = 0';
	}
	$sql .= '   WHERE id='.$id.';';
	sql_query($sql);
}


function member_update_password($id, $pwd)
{
	if (isset($_COOKIE['adr-password'])) {
		setcookie('adr-password', $pwd, COOKIE_EXPIRE);
	}

	if(valid_email($id)) {
		$pwd = mysql_real_escape_string($pwd);
		$sql = 'UPDATE Member
				SET password = "'.$pwd.'"
				WHERE email = "'.$id.'";';

	}
	else {
		$id = intval($id);
		$pwd = mysql_real_escape_string($pwd);
		$sql = 'UPDATE Member
				SET password = "'.$pwd.'"
				WHERE id = '.$id.';';
	}
	sql_query($sql);
}


/**
 * Enregistrer le parent responsable d'un membre mineur
 * @param id_member: id du membre mineur
 */
function member_set_parent($id_member, $pname, $pfirstname, $emergency_phone, $pemail)
{
	$id_member = intval($id_member);
	$pname = mysql_real_escape_string($pname);
	$pfirstname = mysql_real_escape_string($pfirstname);
	$emergency_phone = mysql_real_escape_string($emergency_phone);
	$pemail = mysql_real_escape_string($pemail);
	$sql = 'INSERT INTO Parent (name, firstname, emergency_phone, email)
	        VALUES ("'.$pname.'", "'.$pfirstname.'", "'.$emergency_phone.'", "'.$pemail.'");';
	sql_query($sql);
	$sql = 'UPDATE Member
	        SET idParent = LAST_INSERT_ID()
	        WHERE id = '.$id_member.';';
	sql_query($sql);
}


/**
 * Mise à jour du parent d'un membre
 */
function member_update_parent($id, $name, $firstname, $emergency_phone, $email)
{
	$id = intval($id);
	$name = mysql_real_escape_string($name);
	$firstname = mysql_real_escape_string($firstname);
	$emergency_phone = mysql_real_escape_string($emergency_phone);
	$email = mysql_real_escape_string($email);
	$sql = 'UPDATE Parent
	        SET name = "'.$name.'",
	            firstname = "'.$firstname.'",
	        	emergency_phone = "'.$emergency_phone.'",
	        	email = "'.$email.'"
	        WHERE id = '.$id.';';
	sql_query($sql);
}


/**
 * Supprimer un parent
 */
function member_delete_parent($id)
{
	$id = intval($id);
	$sql = 'DELETE FROM Parent
	        WHERE id = '.$id.';';
	sql_query($sql);
}


/**
 * Détermine si une adresse email est réservée
 * @param email: email à tester
 * @return true si l'email est déjà enregistré, sinon false
 */
function member_email_exists($email)
{
	$email = mysql_real_escape_string($email);
	$sql = 'SELECT m.id
	        FROM Member m
	        WHERE m.email = "'.$email.'";';
	$data = sql_query($sql);
	return mysql_num_rows($data);
}


/**
 * Supprimer un membre (et son parent s'il est mineur)
 * @param id: id du membre à supprimer
 * @return true si succès de la suppression, sinon false
 */
function member_delete($id)
{
	$id = intval($id);
	$member = member_get_by_id($id);
	if ($member != NULL) {
		// suppresion du parent
		if ($member['idParent'] != 0) {
			member_delete_parent($member['idParent']);
		}
		// suppression des décisions aux événements
		$sql = 'DELETE FROM EventDecision
		        WHERE idMember = '.$id.';';
		sql_query($sql);

		// suppression de son historique
		$sql = 'DELETE FROM MemberStatusHistory
		        WHERE user_id = '.$id.';';
		sql_query($sql);

		// suppression du membre
		$sql= 'DELETE FROM Member
		       WHERE id = '.$id.';';
		sql_query($sql);
		return true;
	}
	return false;
}

function member_set_medical_certificate($id)
{
	$id = intval($id);
	$sql = 'UPDATE Member
	        SET date_medical_certificate = '.time().'
	        WHERE id = '.$id.';';
	return sql_query($sql);
}

function member_unset_medical_certificate($id)
{
	$id = intval($id);
	$sql = 'UPDATE Member
	        SET date_medical_certificate = null
	        WHERE id = '.$id.';';
	return sql_query($sql);
}

function member_set_registration_check($id)
{
	$id = intval($id);
	$sql = 'UPDATE Member
	        SET date_registration_check = '.time().'
	        WHERE id = '.$id.';';
	return sql_query($sql);
}

function member_unset_registration_check($id)
{
	$id = intval($id);
	$sql = 'UPDATE Member
	        SET date_registration_check = null
	        WHERE id = '.$id.';';
	return sql_query($sql);
}


/**
 * Générer un fichier CSV contenant la liste des membres
 * @param flag: bitmask des statuts demandés
 * @param filename: nom du fichier à générer
 * @param sep: caractère de séparation (",", ";")
 * @return true si le fichier a été créé, sinon false
 */
function member_to_csv($flag, $filename, $sep)
{
	$file = fopen($filename, 'w');
	if ($file) {
		$members = member_get_list($flag);
		if ($row = mysql_fetch_assoc($members)) {
			fputs($file, 'Nom'.$sep.'Prénom'.$sep.'Adresse'.$sep.'Code postal'.$sep.'Ville'.$sep.'E-mail'.$sep.'Téléphone fixe'.$sep.'Téléphone portable'.$sep.'Date de naissance'.$sep.'Age'.$sep.'Date demande inscription'.$sep.'Date validation inscrition'.$sep.'Gymnase'.$sep.'Créneau'.$sep.'Statut'."\n");
			do {
				// suppression du caractère de séparation dans les champs
				foreach ($row as $key => $value) {
					$row[$key] = str_replace($sep, '', $value);
				}
				$homephone = $row['homephone'] != 0 ? $row['homephone'] : '(non renseigné)';
				$cellphone = $row['cellphone'] != 0 ? $row['cellphone'] : '(non renseigné)';
				$registration = $row['date_registration'] != 0 ? time_to_str($row['date_registration']) : '(inscription non validée)';
				$age = valid_date($row['birth']) ? get_age($row['birth']) : '';
				fputs($file, $row['name'].$sep.$row['firstname'].$sep.$row['address'].$sep.$row['postal_code'].$sep.$row['city'].$sep.$row['email'].$sep.$homephone.$sep.$cellphone.$sep.$row['birth'].$sep.$age.$sep.time_to_str($row['date_pre_registration']).$sep.$registration.$sep.$row['gym_name'].$sep.$row['cat_name'].$sep.$row['status_label']."\n");
			} while ($row = mysql_fetch_assoc($members));
			fclose($file);
			return true;
		}
	}
	return false;
}


/**
 * Obtenir les codes des statuts utilisés, avec le nombre de membres par statut
 * @return record set
 */
function member_status_list()
{
	$sql = 'SELECT s.id, s.code, s.label, s.color, COUNT(m.id) as nb_members
	        FROM MemberStatus s
	        LEFT JOIN Member m ON m.status_id = s.id
			GROUP BY s.id';
	$data = sql_query($sql);
	return $data;
}


/**
 * Obtenir les détails du parent d'un membre mineur
 * @param id_parent: id du parent
 * @return array si existe, sinon NULL
 */
function member_get_parent($id_parent)
{
	$id_parent = intval($id_parent);
	$sql = 'SELECT p.name, p.firstname, p.emergency_phone, p.email
	        FROM Parent p
	        WHERE p.id = '.$id_parent.';';
	$data = sql_query($sql);
	return mysql_fetch_assoc($data);
}


/**
 * Obtenir la liste des membres gérés sur le compte mail d'un autre membre
 * @param id: id du membre proriétaire du mail
 * @param email: email identifiant le compte mail
 */
function member_get_managed_members($id, $email)
{
	$email = mysql_real_escape_string($email);
	$id = intval($id);
	$sql = 'SELECT m.*, s.label as status_label, s.code as status_code, s.color as status_color
	        FROM Member m
			JOIN MemberStatus s on s.id = m.status_id
	        WHERE m.email = "'.$email.'" AND m.id != '.$id.';';
	$data = sql_query($sql);
	return $data;
}


/**
 * Mettre à jour les champs relatifs aux connexions d'un utilisateur
 * @param id: l'id du membre qui vient de se connecter
 * @return true si la requête s'est executée avec succès, false sinon
 */
function member_update_connections($id)
{
	$now = time();
	$sql = 'UPDATE Member
			SET connection_count = connection_count + 1,
				last_connection = '.$now.'
			WHERE id = '.$id.';';
	return sql_query($sql);
}


/* Envoi des emails ---------------------------------------- */


function xmail($receiver, $object, $message)
{
	// préparation envoyeur et destinataire
	$param_object = appparam_get('MAIL_ADRE_SENDER');
	$adrenaline = appparam_read_val($param_object['value']);
	$sender = DEFAULT_MAIL;

	// en-têtes
	$headers  = 'MIME-Version: 1.0'."\n";
    $headers .= 'From: "'.$adrenaline.'"<'.$sender.'>'."\n";
    $headers .= 'Reply-To: '.$sender."\n";
    $headers .= 'Content-Type: text/html; charset="utf-8"'."\n";
    $headers .= 'Content-Transfer-Encoding: 8bit';

	// envoi du mail et retour du résultat (true ou false)
	return mail($receiver, $object, $message, $headers);
}


/**
 * Envoie un mail pour notifier le succès d'une inscription
 * @param email: l'email de la personne à qui envoyer le mail
 * @param name,firstname: les nom et prénom de la personne qui recevra le mail
 * @param new_mail: true si le mail est nouveau dans la BDD (-> envoie du password)
 * @return true si le mail a bien été envoyé, false sinon
 */
function send_registration_mail($email, $name, $firstname, $password, $new_mail)
{
	// preparation de l'objet
	$param_object = appparam_get('MAIL_INSC_OBJECT');
	$object = appparam_read_val($param_object['value']);

	$replacements = array('name'=>$name,'firstname'=>$firstname,'email'=>$email);

	// préparation du contenu du mail avec ajout de l'extension si nécessaire
	if ($new_mail) {
		$replacements['password'] = $password;
		$param_ext = appparam_get('MAIL_INSC_EXT');
		$extension = "\n".appparam_read_val($param_ext['value'],$replacements)."\n";
		$replacements['extension'] = $extension;
	}
	else {
		$replacements['extension'] = '';
	}
	$param_content = appparam_get('MAIL_INSC_CONTENT');
	$content = appparam_read_val($param_content['value'],$replacements);

	// envoi du mail et retour du résultat (true ou false)
	return xmail($email, $object, nl2br($content));
}


/**
 * Envoie un mail pour notifier la confirmation d'une inscription (validation par un admin)
 * @param email: l'email de la personne à qui envoyer le mail
 * @param name,firstname: les nom et prénom de la personne qui recevra le mail
 * @param password: le mot de passe généré aléatoirement à envoyer à l'adhérent
 * @return true si le mail a bien été envoyé, false sinon
 */
function send_validation_mail($email, $name, $firstname)
{
	// preparation de l'objet
	$param_object = appparam_get('MAIL_VALID_OBJECT');
	$object = appparam_read_val($param_object['value']);

	// préparation du contenu du mail
	$param_content = appparam_get('MAIL_VALID_CONTENT');
	$content = appparam_read_val($param_content['value'],array('name'=>$name,'firstname'=>$firstname,'email'=>$email));

	// envoi du mail et retour du résultat (true ou false)
	return xmail($email, $object, nl2br($content));
}


/**
 * Envoie un mail pour notifier la modification des infos d'un membre
 * @param email: l'email de la personne à qui envoyer le mail
 * @param name,firstname: les nom et prénom de la personne qui recevra le mail
 * @return true si le mail a bien été envoyé, false sinon
 */
function send_modification_mail($email, $name, $firstname)
{
	// preparation de l'objet
	$param_object = appparam_get('MAIL_MODIF_OBJECT');
	$object = appparam_read_val($param_object['value']);

	// préparation du contenu du mail
	$param_content = appparam_get('MAIL_MODIF_CONTENT');
	$content = appparam_read_val($param_content['value'],array('name'=>$name,'firstname'=>$firstname,'email'=>$email));

	// envoi du mail et retour du résultat (true ou false)
	return xmail($email, $object, nl2br($content));
}


/**
 * Vérifie si un membre existe en fonction de ses mail, nom, prénom, date de naissance
 * @param email, name, firstname, birth: les infos du membre recherché
 * @return true si un membre correspondant aux critères existe, false sinon
 */
function member_exists($email, $name, $firstname, $birth)
{
	$sql = "SELECT id
			FROM Member
			WHERE email = '$email'
			AND name = '$name'
			AND firstname = '$firstname'
			AND birth = '$birth';";

	return mysql_num_rows(sql_query($sql));
}


// Statistiques -------------------------------------------------------

// répartion des membres par catégorie de créneau horaire
function member_stats_category()
{
	$status_id = status_get_from_code('MEMBER');
	$sql = 'select c.name as cat_name, COUNT(m.id) as member_count
			from Category c
			left join Member m on m.idCategory = c.id
			where m.status_id = '.$status_id.'
			group by c.id
			order by cat_name';
	$data = sql_query($sql);
	return $data;
}

// répartion des membres par sexe
function member_stats_gender()
{
	$status_id = status_get_from_code('MEMBER');
	$sql = 'select gender, COUNT(m.id) as member_count
			from Member m
			where m.status_id = '.$status_id.'
			group by gender
			order by gender desc;';
	$data = sql_query($sql);
	return $data;
}

// répartition des membres par ville
function member_stats_city()
{
	$status_id = status_get_from_code('MEMBER');
	$sql = 'select city, COUNT(m.id) as member_count
			from Member m
			where m.status_id = '.$status_id.'
			group by city
			order by city desc;';
	$data = sql_query($sql);
	return $data;
}
